package com.thinkgem.jeesite.modules.data.rep.utils;

import com.thinkgem.jeesite.common.persistence.Page;
import com.thinkgem.jeesite.common.utils.DateUtils;
import com.thinkgem.jeesite.common.utils.IdGen;
import com.thinkgem.jeesite.common.utils.SpringContextHolder;
import com.thinkgem.jeesite.common.utils.StringUtils;
import com.thinkgem.jeesite.common.utils.excel.ExportExcel;
import com.thinkgem.jeesite.modules.data.rep.entity.CellValue;
import com.thinkgem.jeesite.modules.data.rep.entity.ReportDownLoad;
import com.thinkgem.jeesite.modules.data.rep.service.ReportConfService;
import com.thinkgem.jeesite.modules.sys.entity.User;
import com.thinkgem.jeesite.modules.sys.utils.UserUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.lang.reflect.Method;
import java.net.URLDecoder;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;


public class ExportExcelUtil {

	private static Logger log = Logger.getLogger(ExportExcelUtil.class);
	private final static String templatePath = "exportExcel";

	private int beginRow = 0;

	private int maxColumn = 0;

	private Map<Integer, CellValue> cellMap = new HashMap<Integer, CellValue>();

	private String filePath;

	private List<? extends Object> dataList = new ArrayList<Object>();

	private OutputStream out;

	private XSSFWorkbook workbook;

	private XSSFSheet sheet;

	public static ExecutorService executorService = Executors.newFixedThreadPool(10);
	//private static final CaptchaService captchaService = (CaptchaService) SpringContextHolder.getBean(CaptchaService.class);
	private static final ReportConfService reportConfService = (ReportConfService) SpringContextHolder.getBean(ReportConfService.class);

	public ExportExcelUtil(String filePath, List<? extends Object> dataList, OutputStream out) {
		this.filePath = filePath;
		this.dataList = dataList;
		this.out = out;
	}


	/**
	 * 测试
	 */
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		String filePath = ExportExcelUtil.class.getClassLoader().getResource(templatePath + "/" + "平台运营.xlsx").toString();
		filePath = filePath.replace("file:/", "");

		filePath = URLDecoder.decode(filePath, "UTF-8");
		System.out.println(filePath);
		XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(filePath));
		workbook.getSheetName(0);
	}

	public void exportExcelToOut() throws Exception {
		workbook = new XSSFWorkbook(new FileInputStream(filePath));
		sheet = workbook.getSheetAt(0);
		boolean flag = false;
		for (Iterator<Row> iter = (Iterator<Row>) sheet.rowIterator(); iter.hasNext(); ) {
			Row row = iter.next();
			for (Iterator<Cell> iter2 = (Iterator<Cell>) row.cellIterator(); iter2.hasNext(); ) {
				Cell cell = iter2.next();
				String content = cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC ? cell.getNumericCellValue() + "" : cell.getStringCellValue();
				if (content != null && content.trim().startsWith("{") && content.trim().endsWith("}")) {
					content = content.trim();
					flag = true;
					beginRow = row.getRowNum();
					int column = cell.getColumnIndex();
					CellValue cellValue = new CellValue();

					String str = content.replace("{", "");
					str = str.replace("}", "");
					String[] vals = str.split(",");

					String val = vals[0];
					if (vals.length > 1) {
						cellValue.setDataType(Integer.parseInt(vals[1]));
					}
					if (vals.length > 2) {
						cellValue.setAlignType(Integer.parseInt(vals[2]));
					}

					if (val.startsWith("$data.")) {
						cellValue.setVar(true);
						cellValue.setValueName(val.replace("$data.", ""));
					} else {
						cellValue.setVar(false);
						cellValue.setValueName(val);
					}

					cellMap.put(column, cellValue);
					if (column >= maxColumn) {
						maxColumn = column;
					}
				}
			}
			if (flag) {
				break;
			}
		}
		exportData();

	}

	private void exportData() throws Exception {
		int rowCount = beginRow;
		XSSFRow row = null;
		XSSFCell cell = null;

		XSSFCellStyle cellStyle = workbook.createCellStyle();

		for (int i = 0; i < dataList.size(); i++) {

			row = sheet.createRow(rowCount + i);

			Object data = dataList.get(i);


			int cellCount = 0;
			while (cellCount <= maxColumn) {
				CellValue cellValue = cellMap.get(cellCount);
				if (cellValue != null) {
					//常量
					cell = row.createCell(cellCount);

					String valueName = cellValue.getValueName();


					Object invokeValue = null;
					if (CellValue.NUMBER == cellValue.getDataType()) {
						cell.setCellType(Cell.CELL_TYPE_NUMERIC);
						if (!cellValue.isVar()) {
							cell.setCellValue(Double.parseDouble(valueName));
						} else {
							Method method = data.getClass().getMethod("get" + initialString(valueName));
							invokeValue = method.invoke(data);
							if (invokeValue == null) {
								invokeValue = "0";
							}
							cell.setCellValue(Double.parseDouble(invokeValue.toString()));

						}

					} else {
						cell.setCellType(Cell.CELL_TYPE_STRING);
						if (!cellValue.isVar()) {
							cell.setCellValue(valueName);
						} else {
							Method method = data.getClass().getMethod("get" + initialString(valueName));
							invokeValue = method.invoke(data);
							if (invokeValue == null) {
								invokeValue = "";
							}

							if (invokeValue != "" && invokeValue instanceof java.util.Date) {
								cell.setCellValue(DateUtils.formatDate((java.util.Date) invokeValue, "yyyy-MM-dd HH:mm:ss"));
							} else if (invokeValue != "" && invokeValue instanceof Long) {
								cell.setCellType(Cell.CELL_TYPE_NUMERIC);

								cell.setCellValue(Double.parseDouble(invokeValue.toString()));

							}else if(invokeValue != null && !"".equals(invokeValue.toString()) && isNumeric(invokeValue.toString())){
								cell.setCellType(Cell.CELL_TYPE_NUMERIC);

								cell.setCellValue(Double.parseDouble(invokeValue.toString()));
							}else{
								cell.setCellValue(invokeValue.toString());
							}
						}
					}


					cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
					cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
					cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
					cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框

					//对齐方式
					if (CellValue.LEFT == cellValue.getAlignType()) {
						cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
					} else if (CellValue.RIGHT == cellValue.getAlignType()) {
						cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
					} else {
						cellStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
					}
					cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

					cell.setCellStyle(cellStyle);
				}
				cellCount++;
			}
		}
		workbook.write(out);
		out.flush();
		out.close();
	}

	public String initialString(String str) {
		if (str == null || "".equals(str)) {
			return "";
		}
		return str.substring(0, 1).toUpperCase() + str.substring(1);
	}


	public int getBeginRow() {
		return beginRow;
	}

	public void setBeginRow(int beginRow) {
		this.beginRow = beginRow;
	}

	public Map<Integer, CellValue> getCellMap() {
		return cellMap;
	}

	public void setCellMap(Map<Integer, CellValue> cellMap) {
		this.cellMap = cellMap;
	}

	/**
	 * 上传到tfs
	 *
	 * @param path
	 * @return
	 */
	public static String uploadTfs(String path) {
		File file = new File(path);
		InputStream is = null;
		String newName = "";
		try {
			is = new FileInputStream(file);
			//newName = TFSClient.uploadFile(is, ".xlsx", "");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return newName;
	}

	public static boolean delTfsFile(String tfsFileName) {
		return true;//TFSClient.deleteFile(tfsFileName);
	}

	/**
	 * 生成文件上传tfs
	 *
	 * @param filePath   模板文件路径
	 * @param list       数据
	 * @param reportName 报表名称
	 */
	public static void writeFile(final User user, final String filePath, final List<? extends Object> list,
                                 final String reportName, ReportDownLoad reportDownLoad) {
		writeFile(user,filePath, list, reportName, reportDownLoad, reportName);
	}

	/**
	 * 生成文件上传tfs
	 *
	 * @param filePath     模板文件路径
	 * @param list         数据
	 * @param reportName   报表名称
	 * @param templateName 模板文件名
	 */
	public static void writeFile(final User user, final String filePath, final List<? extends Object> list,
                                 final String reportName, ReportDownLoad reportDownLoad, String templateName) {

		final String outFilePath = System.getProperty("java.io.tmpdir") + "/" + System.currentTimeMillis() + ".xlsx";
		File outFile = new File(outFilePath);
		FileOutputStream outputStream = null;
		try {
			//开始写excel,上传tfs
			//模板文件名
			String newFilePath = filePath + File.separator + templateName + ".xlsx";
			outputStream = new FileOutputStream(outFile);
			log.info("newFilePath:" + newFilePath);
			new ExportExcelUtil(newFilePath, list, outputStream).exportExcelToOut();
			String fileName = ExportExcelUtil.uploadTfs(outFilePath);
			//更新任务状态
			if (fileName.contains("null.xlsx")) {
				reportDownLoad.setStatus((short) 2);
			} else {
				reportDownLoad.setStatus((short) 1);
			}
			reportDownLoad.setFilePath(fileName);
			int i = reportConfService.updateReportDownLoad(reportDownLoad);
			// 如果更新为0，用户删除了任务时，则删除tfs上文件
			if (i == 0) {
				ExportExcelUtil.delTfsFile(fileName);
			} else {
				if (StringUtils.isNotEmpty(user.getPhone())) {
					String msg = "您的报表：<" + reportName + ">导出任务已处理完，请到任务列表下载。";
					//captchaService.sendSms(user.getPhone(), msg, "");
				}
			}
		} catch (Exception e) {
			updateFailReportDownLoad(reportDownLoad);
			log.error("writeFile:", e);
		} finally {
			if (outputStream != null) {
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			//删除临时文件
			outFile.delete();
		}


	}

	/**
	 * 文件写入,上传tfs
	 * @param reportName User user = UserUtils.getUser();
	 */
	public static void uploadFile(String reportName,
								  ReportDownLoad reportDownLoad,

								  String outFilePath) {
		try {
			User user = UserUtils.getUser();
			String fileName = ExportExcelUtil.uploadTfs(outFilePath);
			//更新任务状态
			if (fileName.contains("null.xlsx")) {
				reportDownLoad.setStatus((short) 2);
			} else {
				reportDownLoad.setStatus((short) 1);
			}
			reportDownLoad.setFilePath(fileName);
			int i = reportConfService.updateReportDownLoad(reportDownLoad);
			// 如果更新为0，用户删除了任务时，则删除tfs上文件
			if (i == 0) {
				ExportExcelUtil.delTfsFile(fileName);
			} else {
				if (StringUtils.isNotEmpty(user.getPhone())) {
					String msg = "您的报表：<" + reportName + ">导出任务已处理完，请到任务列表下载。";
					//captchaService.sendSms(user.getPhone(), msg, "");
				}
			}
		} catch (Exception e) {
			updateFailReportDownLoad(reportDownLoad);
			log.error("uploadFile:", e);
		}
	}


    public static void uploadFileTodo(String reportName, ReportDownLoad reportDownLoad, String fileName) {
        User user = UserUtils.getUser();
        //更新任务状态
        if (fileName.contains("null.xlsx")) {
            reportDownLoad.setStatus((short) 2);
        } else {
            reportDownLoad.setStatus((short) 1);
        }
        reportDownLoad.setFilePath(fileName);
        int i = reportConfService.updateReportDownLoad(reportDownLoad);
        // 如果更新为0，用户删除了任务时，则删除tfs上文件
        if (i == 0) {
            ExportExcelUtil.delTfsFile(fileName);
        } else {
            if (StringUtils.isNotEmpty(user.getPhone())) {
                String msg = "您的报表：<" + reportName + ">导出任务已处理完，请到任务列表下载。";
                //captchaService.sendSms(user.getPhone(), msg, "");
            }
        }
    }


	/**
	 * 更新失败状态
	 *
	 * @param reportDownLoad
	 */
	public static void updateFailReportDownLoad(ReportDownLoad reportDownLoad) {
		reportDownLoad.setStatus((short) 2);
		reportDownLoad.setFilePath("数据生成失败！");
		reportConfService.updateReportDownLoad(reportDownLoad);
	}

	public static void saveReportDownLoad(ReportDownLoad reportDownLoad, User user, String reportName) {
		String id = IdGen.uuid();
		reportDownLoad.setId(id);
		reportDownLoad.setUserId(user.getId());
		reportDownLoad.setReportName(reportName);
		reportDownLoad.setStatus((short) 0);
		reportConfService.saveReportDownLoad(reportDownLoad);
	}

    public static String saveReportDownLoadAndRetun(ReportDownLoad reportDownLoad, User user, String reportName) {
        String id = IdGen.uuid();
        reportDownLoad.setId(id);
        reportDownLoad.setUserId(user.getId());
        reportDownLoad.setReportName(reportName);
        reportDownLoad.setStatus((short) 0);
        reportConfService.saveReportDownLoad(reportDownLoad);
        return id;
    }


	/**
	 * 判断字符串是否是数字
	 *
	 * @param str
	 * @return
	 */
	public static boolean isNumeric(String str) {
		if (str == null || str.length() == 0) {
			return false;
		}
		for (int i = 0; i < str.length(); i++) {
			if (!Character.isDigit(str.charAt(i))) {
				return false;
			}
		}
		return true;

	}
	
	/**
	 * 
	 * asynExport2Excel: 异步生成Excel文件并提供下载
	 *
	 * @param  @param reportInfo
	 * @param  @param exportExcel    设定文件
	 * @return void    DOM对象
	 * @throws 
	 * @since  CodingExample　Ver 1.1
	 */
	public static void asynExport2Excel(ReportDownLoad reportInfo, ExportExcel exportExcel) {
        final String tmpFilePath = System.getProperty("java.io.tmpdir") + File.separator
                + System.currentTimeMillis() + ".xlsx";
        File tmpFile = new File(tmpFilePath);
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(tmpFile);
            exportExcel.write(outputStream);
            exportExcel.dispose();

            uploadFile(reportInfo.getReportName(), reportInfo, tmpFilePath);
        } catch (Exception e) {
            updateFailReportDownLoad(reportInfo);
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            tmpFile.delete();
        }
    }

	/**
	 * 
	 * checkRepeatExportTask: 检查是否有重复导出任务
	 *
	 * @param  @param reportInfo
	 * @param  @param userId
	 * @param  @param reportName
	 * @param  @return
	 * @return boolean    DOM对象
	 * @throws 
	 * @since  CodingExample　Ver 1.1
	 */
	public static boolean checkRepeatExportTask(ReportDownLoad reportInfo, String userId, String reportName) {
	    reportInfo.setUserId(reportInfo.getUserId());
        reportInfo.setReportName(reportName);
        reportInfo.setStatus((short) 0);
        Page<ReportDownLoad> page = reportConfService.findPage(reportInfo);
        if (page.getList().size() > 0) {
            return true;
        }
        
        return false;
    }
}
